﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Model;


namespace DAL
{

    public class QinterviewDAL
    {
        Database db = DatabaseFactory.CreateDatabase("constring");
        /// <summary>
        /// 根据公司搜索显示分页
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="Firm_Name"></param>
        /// <returns></returns>
        public List<Interview> PageShow(int pageindex, int pagesize, string Login_Nickname)
        {
            try
            {
               
                int start = (pageindex - 1) * pagesize + 1;
                int end = pagesize * pageindex;

                string str = "select * from ( select ROW_NUMBER() over(order by interview_id)r,Interview.interview_del,Interview.interview_id,Interview.interview_data,Interview.interview_tapes,Interview.interview_image,Interview.interview_attached,Interview.interview_integrity,position.position_name,position.position_id,Firm.Firm_Id,Firm.Firm_Name,Users.Login_Name,Users.Login_Id,Users.Login_Nickname,Users.Login_Pwd,Rated.Rated_Id,Rated.Rated_Content,Rated.Rated_Request from Interview join position on Interview.position_id=position.position_id join Firm on Firm.Firm_Id=Interview.Firm_Id join Users on Interview.User_id=Users.Login_Id join Rated on Rated.Rated_Id=Interview.Rated_Id where Interview.interview_del=1 ";

                if (Login_Nickname != null && Login_Nickname != "")
                {
                    str += " and Users.Login_Nickname like ('%" + Login_Nickname + "%') ";
                }

                str += "  )b where r between " + start + " and " + end + "";
                DataSet ds = db.ExecuteDataSet(CommandType.Text, str);
                
                List<Interview> list = new List<Interview>();

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Interview Intervie = new Interview();
                    Intervie.Firm_Id = Convert.ToInt32(dr["Firm_Id"]);
                    Intervie.interview_attached = dr["interview_attached"].ToString();
                    Intervie.interview_data = dr["interview_data"].ToString();
                    Intervie.interview_del = Convert.ToInt32(dr["interview_del"]);
                    Intervie.interview_id = Convert.ToInt32(dr["interview_id"]);
                    Intervie.interview_image = dr["interview_image"].ToString();
                    Intervie.interview_integrity = dr["interview_integrity"].ToString();
                    Intervie.interview_tapes = dr["interview_tapes"].ToString();
                    Intervie.position_id = Convert.ToInt32(dr["position_id"]);
                    Intervie.Rated_Id = Convert.ToInt32(dr["Rated_Id"]);
                    Intervie.Firm_Name = dr["Firm_Name"].ToString();
                    Intervie.position_name = dr["position_name"].ToString();
                    Intervie.Rated_Content = dr["Rated_Content"].ToString();
                    Intervie.Rated_Request = dr["Rated_Request"].ToString();
                    Intervie.Login_Name = dr["Login_Name"].ToString();
                    Intervie.Login_Pwd = dr["Login_Pwd"].ToString();
                    Intervie.Login_Nickname = dr["Login_Nickname"].ToString();
                    list.Add(Intervie);
                }
                return list;
            }
            catch (Exception)
            {
                throw;
            }
           
        }
        /// <summary>
        /// 根据公司搜索显示分页总个数
        /// </summary>
        /// <param name="Firm_Name">公司名称</param>
        /// <returns></returns>

        public int count(string Login_Nickname)
        {

            string str = "  select COUNT(*) from Interview where Interview.interview_del=1  ";
            if (Login_Nickname != null && Login_Nickname != "")
            {
                str += " and Users.Login_Nickname like ('%" + Login_Nickname + "%')  ";
            }
            int a = Convert.ToInt32(db.ExecuteScalar(CommandType.Text, str));
            return a;
        }
        /// <summary>
        /// 分页显示我发布的面试试题
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="Firm_Name"></param>
        /// <returns></returns>
        public List<Interview> PageShowTest(int pageindex, int pagesize, int users_id)
        {
            try
            {
                int start = (pageindex - 1) * pagesize + 1;
                int end = pagesize * pageindex;
                StringBuilder str = new StringBuilder();
                str.AppendLine(" select * from ");
                str.AppendLine(" ( ");
                str.AppendLine(" select ROW_NUMBER() over(order by interview_id)r, ");
                str.AppendLine(" Interview.interview_del,Interview.interview_id, ");
                str.AppendLine(" Interview.interview_data,Interview.interview_tapes, ");
                str.AppendLine(" Interview.interview_image,Interview.interview_attached, ");
                str.AppendLine(" Interview.interview_integrity,position.position_name,position.position_id, ");
                str.AppendLine(" Firm.Firm_Id,Firm.Firm_Name,Users.Login_Name,Users.Login_Id,Users.Login_Nickname, ");
                str.AppendLine(" Users.Login_Pwd,Rated.Rated_Id,Rated.Rated_Content,Rated.Rated_Request");
                str.AppendLine(" from Interview join position  ");
                str.AppendLine(" on Interview.position_id=position.position_id join Firm ");
                str.AppendLine(" on Firm.Firm_Id=Interview.Firm_Id join Users  ");
                str.AppendLine(" on Interview.User_id=Users.Login_Id join Rated  ");
                str.AppendLine(" on Rated.Rated_Id=Interview.Rated_Id  where users_id=" + users_id + "");
                str.AppendLine(" )b ");
                str.AppendLine(" where r between ");
                str.AppendLine(" " + start + " and " + end + "");
                DataSet ds = db.ExecuteDataSet(CommandType.Text, str.ToString());
                List<Interview> list = new List<Interview>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Interview Intervie = new Interview();
                    Intervie.Firm_Id = Convert.ToInt32(dr["Firm_Id"]);
                    Intervie.interview_attached = dr["interview_attached"].ToString();
                    Intervie.interview_data = dr["interview_data"].ToString();
                    Intervie.interview_del = dr["interview_del"].ToString();
                    Intervie.interview_id = Convert.ToInt32(dr["interview_id"]);
                    Intervie.interview_image = dr["interview_image"].ToString();
                    Intervie.interview_integrity = dr["interview_integrity"].ToString();
                    Intervie.interview_tapes = dr["interview_tapes"].ToString();
                    Intervie.position_id = Convert.ToInt32(dr["position_id"]);
                    Intervie.Rated_Id = Convert.ToInt32(dr["Rated_Id"]);
                    Intervie.Firm_Name = dr["Firm_Name"].ToString();
                    Intervie.position_name = dr["position_name"].ToString();

                    Intervie.Rated_Content = dr["Rated_Content"].ToString();
                    Intervie.Rated_Request = dr["Rated_Request"].ToString();
                    Intervie.Login_Name = dr["Login_Name"].ToString();
                    Intervie.Login_Pwd = dr["Login_Pwd"].ToString();
                    Intervie.Login_Nickname = dr["Login_Nickname"].ToString();
                    list.Add(Intervie);
                }
                return list;
            }
            catch (Exception)
            {
                throw;
            }

        }
        /// <summary>
        /// 根据我发布的面试试题显示分页总个数
        /// </summary>
        /// <param name="Firm_Name">公司名称</param>
        /// <returns></returns>

        public int countTest(int users_id)
        {

            string str = "  select COUNT(*) from Interview join position on Interview.position_id=position.position_id join Firm on Firm.Firm_Id=Interview.Firm_Id join Users on Interview.User_id=Users.Login_Id join Rated on Rated.Rated_Id=Interview.Rated_Id where users_id=1 ";
            int a = Convert.ToInt32(db.ExecuteScalar(CommandType.Text, str));
            return a;
        }

    }
}
